﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;

namespace DVSProject.Lib
{
    public class ComplainRepo
    {
        public const string m_msg_head = "Complain";
        public IEnumerable<ComplainModels> GetComplain()
        {
            List<ComplainModels> complains = new List<ComplainModels>();
            string SQLCommand = @"select * from TBComplain where Status <>'Delete' order by locationSetCode";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        ComplainModels complain = new ComplainModels();
                        complain.Id = (int)reader["Id"];
                        complain.LocationSetCode = (string)reader["LocationSetCode"];
                        complain.SerialNo = (string)reader["SerialNo"];
                        complain.ComplainDate = (DateTime)reader["ComplainDate"];
                        complain.Punctual = (int)reader["Punctual"];
                        complain.Service = (int)reader["Service"];
                        complain.Cooperation = (int)reader["Service"];
                        complain.Relation = (int)reader["Relation"];
                        complain.Result = (int)reader["Result"];
                        complain.Problem = (string)reader["Problem"];
                        complain.ProblemSolv = (string)reader["ProblemSolv"];
                        complain.Status = (string)reader["Status"];
                        complain.EmpId = (string)reader["EmpId"];
                        complain.CreateBy = (string)reader["CreateBy"];
                        complain.CreateOn = (DateTime)reader["CreateOn"];
                        complain.UpdateBy = (string)reader["UpdateBy"];
                        complain.UpdateOn = (DateTime)reader["UpdateOn"];

                        complains.Add(complain);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return complains.ToList();
        }

        public ComplainModels GetComplainById(int id)
        {
            ComplainModels complain = new ComplainModels();
            string SQLCommand = @"select * from TBComplain wherer Id=@Id ";
            using (var db = new DBHelper())
            {
                try
                {
                  
                    db.AddParameter("@Id", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                       
                        complain.Id = (int)reader["Id"];
                        complain.LocationSetCode = (string)reader["LocationSetCode"];
                        complain.SerialNo = (string)reader["SerialNo"];
                        complain.ComplainDate = (DateTime)reader["ComplainDate"];
                        complain.Punctual = (int)reader["Punctual"];
                        complain.Service = (int)reader["Service"];
                        complain.Cooperation = (int)reader["Service"];
                        complain.Relation = (int)reader["Relation"];
                        complain.Result = (int)reader["Result"];
                        complain.Problem = (string)reader["Problem"];
                        complain.ProblemSolv = (string)reader["ProblemSolv"];
                        complain.Status = (string)reader["Status"];
                        complain.CreateBy = (string)reader["CreateBy"];
                        complain.CreateOn = (DateTime)reader["CreateOn"];
                        complain.UpdateBy = (string)reader["UpdateBy"];
                        complain.UpdateOn = (DateTime)reader["UpdateOn"];

                         
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return complain;
        }

        public bool Create(ComplainModels complain)
        {
            bool result = true;
            string SQLCommand = @"declare @Id int
                                    select @Id= isnull(max(id),0)+1 from tbcomplainTemp 

INSERT into TBComplainTemp(Id,FSR_NO,LocationSetCode,ComplainDate,Status,EmpId,CreateBy,CreateOn,UpdateBy,UpdateOn)
                                VALUES(@Id,@FSR_NO,@LocationSetCode,getdate(),@Status,@UserId,@UserId,getdate(),@UserId,getdate())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@FSR_NO", complain.FSR_No);
                    db.AddParameter("@LocationSetCode", complain.LocationSetCode);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", complain.CreateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool UpdateMachine(string serialNo,string UserID)
        {
            bool result = true;
            string SQLCommand = @"declare @Id int
                                    select @Id= max(id) from tbcomplainTemp 
                                    where createby =@UserID

                                    update TBComplainTemp
                                    SET SerialNo=@SerialNo,
	                                    UpdateBy=@UserId,
	                                    UpdateOn=GETDATE()	
                                    where Id =@Id ";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@SerialNo", serialNo);
                    db.AddParameter("@UserID", UserID);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }

            return result;
        }

        public bool Edit(ComplainModels complain)
        {
            bool result = true;
            string SQLCommand = @"declare @Id int
                                    select @Id= max(id)+1 from tbcomplainTemp 
                                    
                                    INSERT INTO TBComplainTemp (Id,FSR_NO,LocationSetCode,SerialNo,ComplainDate,EmpId,Status,CreateBy,CreateOn)
                                    select @Id,@FSR_NO,LocationSetCode , SerialNo,GETDATE(),@UserId,'Active',@UserId,GETDATE() from TBVisitHD where FSR_NO=@FSR_NO


                                    update TBComplainTemp
                                    SET Punctual=@Punctual,
	                                    Service=@Service,
	                                    Cooperation=@Cooperation,
	                                    Relation=@Relation,
	                                    Result=@Result,
	                                    UpdateBy=@UserId,
	                                    UpdateOn=GETDATE()	
                                    where Id =@Id ";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@FSR_NO", complain.FSR_No);
                    db.AddParameter("@Punctual", complain.Punctual);
                    db.AddParameter("@Service", complain.Service);
                    db.AddParameter("@Cooperation", complain.Cooperation);
                    db.AddParameter("@Relation", complain.Relation);
                    db.AddParameter("@Result", complain.Result);
                    db.AddParameter("@UserID", complain.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }

            return result;
        }

        public bool Delete(int id,string UserID)
        {
            bool result = true;
            string SQLCommand = @"
                                    update TBComplain
                                    SET Status='Delete',
	                                    UpdateBy=@UserId,
	                                    UpdateOn=GETDATE()	
                                    where Id =@Id ";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@Id", id);
                    db.AddParameter("@UserID", UserID);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }

            return result;
        }

        public bool UpdateProblem(ComplainModels complain)
        {
            bool result = true;
            string SQLCommand = @"declare @Id int
                                    select @Id= max(id) from tbcomplainTemp 
                                    where createby =@UserID and Status <>'Complete'

                                    update TBComplainTemp
                                    SET Problem=@Problem,
	                                    ProblemSolv=@ProblemSolv,
	                                    UpdateBy=@UserId,
	                                    UpdateOn=GETDATE()	
                                    where Id =@Id 

                            Insert INTO TBComplain
                             SELECT FSR_No,LocationSetCode,SerialNo,ComplainDate,Punctual,Service,
                            Cooperation,Relation,Result,Problem,ProblemSolv,
                             EmpId,Status,CreateBy,getdate() as CreateOn,UpdateBy,getdate() as UpdateOn
                             FROM TBComplainTemp 
                             where Id=@Id

                            UPDATE TBVisitHD 
                                SET  IsComplain ='1',UpdateBy =@UserID,UpdateOn=GETDATE()
                                where FSR_NO =(select FSR_NO from TBComplainTemp where Id=@Id)
        
                            Delete from  TBComplainTemp
                            where Id=@Id
";
 //update TBComplainTemp
 //                           set Status ='Complete'
 //                           where Id=@Id
            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@Problem", complain.Problem);
                    db.AddParameter("@ProblemSolv", complain.ProblemSolv);
                    db.AddParameter("@UserID", complain.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

    }
}